Transactions and ADO.NET
The ADO.NET Connection object is used to work with transactions. As
you'll see in the next few sections, the Connection object is used
to initiate the transaction. Any command objects that need to enlist in the
transaction are then assigned to the transaction. The transaction object itself
is used to save, roll back, and perform other actions on the transaction.
Starting a Transaction
In ADO.NET, you can start a database transaction by calling the
BeginTransaction() method of the Connection object, as seen in
Listing 18.1.
Listing 18.1 Starting a New Transaction
'Create Connection
Dim conn as SqlConnection = new SqlConnection("Data Source=(local);" +
"Initial Catalog=northwind;UID=sa;PWD=;")
'Create Command
Dim cmd as SqlCommand = new SqlCommand()
cmd.Connection = conn
'Connection must be open to start transaction
conn.Open()
'Create Transaction and apply it to command object
Dim myTrans = conn.BeginTransaction("TransactionName")
NOTE
In order to begin a new transaction, the Connection object must be
open. If you attempt to start a new transaction without first calling the
Open() method of the Connection object, an
InvalidOperationException is thrown.
Notice that the BeginTransaction() method returns a
SqlTransaction object. For the life of the transaction, you will use
this object to manipulate the database transaction, as needed.
At this point, you might be tempted to start executing commands against the
database. However, before you can send any queries to the database using this
transaction, you must assign the transaction object to the Transaction
property of the Command object as seen in the last line of Listing
18.1. This is slightly counterintuitive, because the Command object
already has an associated Connection object assigned, and transactions
operate over a connection. However, you must still remember this step.
CAUTION
Do not forget to assign the SqlTransaction object returned by the
BeginTransaction() method to the Transaction property of the
Command object. If you forget, an InvalidOperationException
will be thrown by your code the first time you attempt to execute a query.
Now you're ready to make some database changes using the
Command object. Feel free to be creative. You can even delete an entire
table, if you like. Because you're executing all these changes in a
transaction, it's very easy to roll back your changes and return to the
table's original state.
CAUTION
Keep in mind that only changes made to the database through this
Connection object will be in the transaction. Therefore, if you were to
modify the data through any other means, such as SQL Enterprise Manager, those
changes will not be rolled back.
Rolling Back a Transaction
As you process each of the individual database changes that comprise your
database transaction, it's possible to undo or roll back any of the changes
you've made to the database since beginning the transaction. For instance,
alluding to the monetary transaction example from earlier this hour, if there is
an error removing the funds from bank account A after placing the money in
account B, you would definitely want to roll back the changes you've made
and either retry the transaction at a later time, or flag the record for later
examination.
To roll a transaction back to its original state, you just call the
Rollback() method of the transaction object, as in Listing 18.2.
Listing 18.2 Rolling a Transaction Back
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<HTML>
<HEAD>
<LINK rel="stylesheet" type="text/css" href="Main.css">
<!-- End Style Sheet -->
<script language="VB" runat="server" >
Sub Page_Load(Source as Object, E as EventArgs)
If IsPostBack then
Dim sOutput as string
'Create Connection
Dim conn as SqlConnection = new SqlConnection("Data Source=" + _
"localhost;Initial Catalog=northwind;UID=sa;PWD=;")
'Create Command
Dim cmd as SqlCommand = new SqlCommand()
cmd.Connection = conn
'Connection must be open to start transaction
conn.Open()
'Create Transaction and apply it to command object
Dim myTrans = conn.BeginTransaction("TransactionName")
cmd.Transaction = myTrans
'Show Database before modifications
GetAndBindData(datagrid1)
'Execute Database Change #1
ExecuteSQL(cmd, "UPDATE Fruits SET Quantity = 4 " + _
"WHERE Name = 'Apple'")
'Execute Database Change #2
Try
ExecuteSQL(cmd, "DELETE FROM FRUITS")
Throw New Exception("A random horrible database error")
myTrans.Commit()
Catch
myTrans.Rollback()
End Try
'Requery to make sure changes are gone
GetAndBindData(datagrid7)
conn.Close()
Else
'--- No post back ---
'Just display the fruit table as it is
GetAndBindData(datagrid1)
End If 'Postback
End Sub
Private Sub ExecuteSQL(cmd as SqlCommand, sSQL as string)
cmd.CommandText = sSQL
cmd.ExecuteNonQuery()
End Sub
Private Sub GetAndBindData( myDataGrid as _
System.Web.UI.WebControls.DataGrid )
Dim conn as SqlConnection = new SqlConnection("Data Source=" + _
localhost;Initial Catalog=Northwind;UID=sa;PWD=; ")
Dim cmd as SqlCommand = new SqlCommand("SELECT name, " + _
"description, quantity FROM Fruits", conn)
conn.Open()
myDataGrid.DataSource = cmd.ExecuteReader()
myDataGrid.DataBind()
conn.Close()
End Sub
</script>
</HEAD>
<BODY>
<h1>Working With Transactions</h1>
<hr>
<form runat="server" id=form1 name=form1>
<asp:label id=lblStatus runat="server" />
<p>
<input type="submit" Value="Run Queries">
<table align="center" cellpadding=10 cellspacing=10>
<tr>
<td colspan=2 valign="center">
<h3>Original Data:</h3>
<asp:datagrid id=datagrid1 runat="server" />
<br><br>
</td>
</tr>
</tr>
<td colspan=2>
<h3>After rolling back transaction to beginning:</h3>
<asp:datagrid id=datagrid7 runat="server" />
</td>
</tr>
</table>
</form>
<hr>
</BODY>
</HTML>
Listing 18.2 creates a transaction, performs some database changes, and then
rolls these changes back. On line 28, the transaction is started using the
BeginTransaction() method of the Connection object. On the
following line, the transaction object is assigned to the Command
object. Any queries executed using the cmd Command object are performed
under the umbrella of this transaction.
The database is then displayed before modifications using the
GetAndBindData() method on line 32. On lines 34–36, the first
database change is made, changing the quantity of apples to 4. Lines 37–43
perform the next database change. Then, an error is simulated using the
Throw() method on line 39, causing the Rollback() method to
run in the Catch code block on line 41. Had there been no error, the
transaction would have been committed and all database changes made final.
However, because the Rollback() method was called instead, all changes
made under this transaction are reverted back to their previous state.
Figure 18.1 The appearance of the
Web form in Listing 18.2 after submitting the form.
As you can see in Figure 18.1, the code
in Listing 18.2 displays a Web form. After you click the button labeled Run
Queries, a connection to the database is opened and a transaction started. Database
change #1 is made, and then the second change is attempted. The second database
change is wrapped in a Try...Catch block. This enables you to check
for an error and handle that error gracefully. In this case, the transaction
is rolled back, canceling any changes. Otherwise, the transaction is saved.
Committing a Transaction
In Listing 18.2, after all database modifications have been successfully
performed, the transaction is saved by using the Commit() method of the
transaction object. This ends the transaction and makes all database changes
permanent.
Canceling a Transaction
If an error had occurred during the second database modification in Listing
18.2, the error would have been caught by the Try...Catch block and the
Catch portion of the code would have been executed. In this case, the
Rollback() method of the transaction object would have been called,
reversing all changes made to the database, leaving it in its original
state.
Saving a Transaction
When working with database transactions, it's possible to save the
transaction at any point. However, the term "saving" when applied to
transactions is a bit misleading. The process is more like bookmarking a site
than saving a file. After saving at a point in a transaction, you can revert
back to that point, canceling any changes you might have made after saving.
In other words, let's say you initiate a transaction, and save the
transaction after updating several hundred fields in the database. Suppose that
later, after several other database modifications, an error occurs. Rather than
rolling all the way back to the beginning, you can save your transaction after
the first batch of queries is executed. Then, later you can roll back your
changes to that saved point. However, this method can be dangerous if any table
relationships are missing, creating an unstable database.
To save a database transaction at any point, you need only call the
Save() method of the transaction object, and pass it the name you would
like to use to reference the saved point. Saving a transaction often makes more
sense when working with hundreds or thousands of database changes in a single
transaction. Without saving, you'd be forced to roll back all of these
changes in the case of an error!
The example in Listing 18.3 demonstrates this concept. Just as in the previous
example, a database connection is opened and a transaction initiated. Some changes
are made to the data in the database, the transaction is saved, some more changes
are made, and then the transaction is rolled back to the saved point. The transaction
is then committed, finalizing all the changes made up to the saved point. Figure
18.2 shows the appearance of the Web form after submitting the form.
Listing 18.3 Rolling a Transaction Back to a Saved Point
<%@ Page Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<HTML>
<HEAD>
<LINK rel="stylesheet" type="text/css" href="Main.css">
<!-- End Style Sheet -->
<script language="VB" runat="server" >
Sub Page_Load(Source as Object, E as EventArgs)
If IsPostBack then
Dim sOutput as string
'Create Connection
Dim conn as SqlConnection = new SqlConnection("Data Source=" + _
"localhost;Initial Catalog=northwind;UID=sa;PWD=;")
'Create Command
Dim cmd as SqlCommand = new SqlCommand()
cmd.Connection = conn
'Connection must be open to start transaction
conn.Open()
'Create Transaction and apply it to command object
Dim myTrans = conn.BeginTransaction("TransactionName")
cmd.Transaction = myTrans
'Show Database before modifications
GetAndBindData(datagrid1)
'Execute Database Change #1
ExecuteSQL(cmd, "UPDATE Fruits SET Quantity = 2 " + _
"WHERE Name = 'Apple'")
'Insert potentially hundreds of database changes here
'Save Transaction
myTrans.Save("SavePoint1")
'Execute Database Change #2
ExecuteSQL(cmd, "DELETE FROM FRUITS")
'Oh no! We've made a horrible mistake.
'Rollback to saved point
myTrans.Rollback("SavePoint1")
'Commit earlier changes
myTrans.Commit()
'Requery
GetAndBindData(datagrid7)
conn.Close()
Else
'--- No post back ---
'Just display the fruit table as it is
GetAndBindData(datagrid1)
End If 'Postback
End Sub
Private Sub ExecuteSQL(cmd as SqlCommand, sSQL as string)
cmd.CommandText = sSQL
cmd.ExecuteNonQuery()
End Sub
Private Sub GetAndBindData( myDataGrid as _
System.Web.UI.WebControls.DataGrid )
Dim conn as SqlConnection = new SqlConnection("Data Source=" + _
"localhost;Initial Catalog=Northwind;UID=sa;PWD=;")
Dim cmd as SqlCommand = new SqlCommand("SELECT name, " + _
"description, quantity FROM Fruits", conn)
conn.Open()
myDataGrid.DataSource = cmd.ExecuteReader()
myDataGrid.DataBind()
conn.Close()
End Sub
</script>
</HEAD>
<BODY>
<h1>Working With Transactions</h1>
<hr>
<form runat="server" id=form1 name=form1>
<asp:label id=lblStatus runat="server" />
<p>
<input type="submit" Value="Run Queries" id=submit1 name=submit1>
<table align="center" cellpadding=10 cellspacing=10>
<tr>
<td colspan=2 valign="center">
<h3>Original Data:</h3>
<asp:datagrid id=datagrid1 runat="server" />
<br><br>
</td>
</tr>
</tr>
<td colspan=2>
<h3>After rolling back transaction to saved point:</h3>
<asp:datagrid id=datagrid7 runat="server" />
</td>
</tr>
</table>
</form>
<hr>
</BODY>
</HTML>
Listing 18.3 is similar to the previous transaction code provided in Listing
18.2. Line 29 starts a transaction that is then assigned to the cmd
Command object on the next line. The current state of the database is
displayed using the GetAndBindData() method on line 33. A database
change is then made on line 36, changing the quantity of apples from 2 to 4.
Shortly after on line 42, the transaction is saved using the Save()
method of the transaction object. The following line of code then deletes all
contents from the Fruit table. For the purposes of this example, that is undesirable.
On line 49, the transaction is rolled back to the previous save point, before
the deletion was performed. Line 52 uses the Commit() method to end
the transaction and commit these database changes. Line 55 again shows the state
of the database. As you can see in Figure
18.2, the quantity of apples is modified, but the results of the DELETE
query were not saved.
Figure 18.2 Saving a database transaction.
| 
View Cart
){kind=link}
){kind=link}